import xlwt
from django.db.models import Count, Sum
from django.http import JsonResponse, HttpResponse
from django.shortcuts import render

from app01.models import 预算实绩
from app01.utils.fieldshtml import fields_to_html, fields_to_save, yearmonth_to_html
from app01.utils.pagination import Pagination


def budget_list(request):
    ym_str =request.GET.get('f')
    if ym_str:
        ym_sel = ym_str.split(',')
        # print(ym_sel)
        list_all = 预算实绩.objects.filter(年月__in=ym_sel).order_by('-年月', '作业区编码', '费用分类')

    else:
        list_all = 预算实绩.objects.all().order_by('-年月', '作业区编码', '费用分类')

    print(list_all.count())
    # 生成是否表格字段选择框HTML代码“select_str”，是否显示字段的配置“dicts”
    select_str = yearmonth_to_html(request.session['info']['employeeid'], 预算实绩)

    # 生成分页HTML代码
    page = Pagination(request, list_all)

    # 生成表格底部的合计栏
    templist = list_all[page.start_recno:page.end_recno]
    sum_row = templist.aggregate(Count('费用分类'), Sum('预算'), Sum('实绩'))
    # print(page.data_list)

    context = {
        'sum_row': sum_row,
        'select': select_str,
        'data_list': page.data_list,  # 要显示的数据记录
        'page_string': page.html(),  # 分页的HTML代码
        'start_recno': page.start_recno,
        'end_recno': page.end_recno,
        'active6': 'active',
        'title': '预算/实绩查询'
    }
    return render(request, 'budget_list.html', context)

def export_excel_budget(request):
    # 开始记录
    start_recno = int(request.GET.get('start_recno', 0))
    # 结束记录
    end_recno = int(request.GET.get('end_recno', 10))

    filename = quote('作业区预算.xls')
    response = HttpResponse(content_type='application/vnd.msexecl')
    response['Content-Disposition'] = f'attachment; filename="{filename}"'

    data_list = 预算实绩.objects.all().order_by('-年月', '作业区编码', '费用分类')[start_recno:end_recno]
    fields = list(data_list.values()[0])

    # 创建工作簿
    xls_file = xlwt.Workbook(encoding='utf8')
    # 添加工作表，同一单元格可重复修改
    sheet = xls_file.add_sheet('Sheet1', cell_overwrite_ok=True)
    for col, title in enumerate(fields):
        sheet.write(0, col, data_list.model._meta.get_field(title).verbose_name)
    row_index = 1
    for row in data_list.values():
        print(row)
        col_index = 0
        for value in row.values():
            sheet.write(row_index, col_index, value)
            col_index += 1
        row_index += 1
    xls_file.save(response)
    return response